package edu.uoc.tdp.servidor.bbdd.informes;

import edu.uoc.tdp.common.EOlympicsException;
import edu.uoc.tdp.bbdd.GestorDiscConnexio;
import edu.uoc.tdp.beans.proves.EstatProva;
import edu.uoc.tdp.beans.proves.Prova;
import edu.uoc.tdp.common.EOlympicsExceptionProva;
import edu.uoc.tdp.common.EOlympicsUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * Classe GestorEstudiants
 * TDP Grup 1X4J
 */
public class GestorInformes {
    
    private GestorDiscConnexio gestor;
    private Connection connexio;

    /**
     * Constructor de la classe Gestora
     * Crea un nou gestor de disc.
     */
    public GestorInformes() throws EOlympicsException {
        this.gestor = new GestorDiscConnexio();
        this.gestor.obreConnexioBD();
        this.connexio = gestor.getConnexio();
    }

    /**
     * Constructor de la classe Gestora
     * Utilitza el gestor de disc passat com a paràmetre
     * @param gdc connexió a la BD de tipus GestorDiscConnexio
     */
    public GestorInformes(GestorDiscConnexio gdc) {
        this.gestor   = gdc;
        this.connexio = gdc.getConnexio();
    }
    
    
    /**
     * Accessor de lectura, recupera l'id i la descripció totes les proves en estat TANCADA
     * @return LLista de proves
     * @throws EOlympicsException 
     */
    public List<Prova> getProvesTancades() throws EOlympicsException{                
        String sql="SELECT P.idProva,P.descripcio "
                + "FROM PROVA P "                
                + "WHERE P.estat = '"  +EstatProva.TANCADA.getEstat() +"'";
        List <Prova>proves= new ArrayList<Prova>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                proves.add(new Prova(rs.getInt(1),rs.getString(2)));
            }
            st.close();
            rs.close();
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return proves;
    }
    
    public ArrayList<HashMap> consultarProves(Integer idUniversitat, Integer idProva, Date dinscripcio_ini,
            Date dinscripcio_fi, Date drealitacio_ini, Date drealitacio_fi) throws EOlympicsException{                
            String sql="SELECT P.descripcio, R.tipusRonda, R.nombrePreguntes, P.dataIniInscripcio, P.dataFiInscripcio, "+
                    " R.dataIniciRealitzacio, R.dataFiRealitzacio, P.nombrePlaces,R.idProva " +
                    " FROM RONDA R, PROVA P " +
                    " WHERE P.idProva = R.idProva "+
                    " AND P.estat = '"  +EstatProva.TANCADA.getEstat() +"'";
            if (idProva!=null){
                sql+=" AND R.idProva = "  +idProva;
            }
            if (dinscripcio_ini!=null){
                sql+=" AND P.dataIniInscripcio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(dinscripcio_ini) +"'";;
            }
            if (dinscripcio_fi!=null){
                sql+=" AND P.dataFiInscripcio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(dinscripcio_fi) +"'";;
            }
            if (drealitacio_ini!=null){
                sql+=" AND R.dataIniciRealitzacio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_ini) +"'";;
            }
            if (drealitacio_fi!=null){
                sql+=" AND R.dataFiRealitzacio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_fi) +"'";;
            }
         
        ArrayList <HashMap>resultats= new ArrayList<HashMap>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                HashMap<String, Object> item = new HashMap<String, Object>();
                item.put("DESCRIPCIO", rs.getString(1));
                item.put("TIPUS", rs.getString(2));
                item.put("NUMPREGUNTES", rs.getInt(3));
                item.put("DINSCRIPCIO_INI", rs.getDate(4));
                item.put("DINSCRIPCIO_FI", rs.getDate(5));
                item.put("DREALITZACIO_INI", rs.getDate(6));
                item.put("DREALITZACIO_FI", rs.getDate(7));
                item.put("NUMPLACES", rs.getInt(8));
                item.put("IDPROVA", rs.getInt(9));
                
                item.put("INSCRITS", getInscritsByProva(idUniversitat,rs.getInt(9) ));
                item.put("PARTICIPANTS", getParticipantsByRonda(idUniversitat,rs.getInt(9), rs.getString(1) ));
                //Maxims
                String sqlMax ="select sum(respostesEncertades),sum(respostesFallades),sum(respostesBlanc), sum(nota) "+
                        "from PARTICIPACIO "+
                        "where idProva = "+rs.getInt(9)+
                        " and tipusRonda ='"+rs.getString(2) +"'";
                if (idUniversitat!=null){
                    sqlMax += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
                }
                Statement stMaxims=gestor.creaSentencia();
                ResultSet rsMaxims=stMaxims.executeQuery(sqlMax);
                while (rsMaxims.next()){  
                    item.put("ENCERTS", rsMaxims.getInt(1));
                    item.put("ERRORS", rsMaxims.getInt(2));
                    item.put("BLANC", rsMaxims.getInt(3));
                    item.put("TOTAL", rsMaxims.getInt(4));
                }
                stMaxims.close();
                rsMaxims.close();
                
                //Notes
                
                String sqlNotes ="select  min(nota), max(nota) "+
                        "from PARTICIPACIO "+
                        "where idProva = "+rs.getInt(9)+
                        " and tipusRonda ='"+rs.getString(2) +"'";
                if (idUniversitat!=null){
                    sqlNotes += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
                }
                System.out.println(sqlNotes);
                Statement stNotes=gestor.creaSentencia();
                ResultSet rsNotes=stNotes.executeQuery(sqlNotes);
                while (rsNotes.next()){  
                    item.put("MIN", rsNotes.getInt(1));
                    item.put("MAX", rsNotes.getInt(2));
                }
                stNotes.close();
                rsNotes.close();
                resultats.add(item);
            }
            st.close();
            rs.close();
            
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return resultats;
    }
    
    /**
     * Retorna el número de incrists en una prova
     * @param idUniversitat
     * @param idProva
     * @return
     * @throws EOlympicsException 
     */
    public Integer getInscritsByProva(Integer idUniversitat, Integer idProva)throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="select count(1) from INSCRIPCIO where idProva ="+idProva;
            if (idUniversitat!=null){
                sql += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
            }
            System.out.println(sql);
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    /**
     * Retorna el nombre de participants a una Ronda
     * @param idUniversitat
     * @param idProva
     * @param ronda
     * @return
     * @throws EOlympicsException 
     */
    public Integer getParticipantsByRonda(Integer idUniversitat, Integer idProva, String ronda)throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="select count(1) from PARTICIPACIO where idProva ="+idProva + " and tipusronda='"+
                  ronda+"' "  ;
            if (idUniversitat!=null){
                sql += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
            }
            System.out.println(sql);
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    
    /**
     * Tracta les excepcions SQL, diferenciant-les pel missatge de l'error
     * @param sqle                      ERROR.message()
     * @throws EOlympicsExceptionProva
     * @throws EOlympicsException 
     */
    private void treatExceptionSQL(String sqle) throws EOlympicsExceptionProva, EOlympicsException{
           if(sqle.contains("pk_prova")) {
               throw new EOlympicsExceptionProva(EOlympicsExceptionProva.ERR_SQL_PK_PROVA);
           }else if(sqle.contains("ck_datainsc")) {
               throw new EOlympicsExceptionProva(EOlympicsExceptionProva.ERR_SQL_CK_PROVA);           
           }
           else{
               System.out.println(sqle);
               throw new EOlympicsException(EOlympicsException.ERR_SQL);
           }
        
    }
    
}
